package com.nhjf.common.util.excel;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.servlet.http.HttpServletResponse;

import com.nhjf.admin.common.util.LogMessage;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;





public class ExcelTool {
	public void orderExcel(List<String> nameList,String url,List<Map>mapList,String createSheet,String laber) throws Exception{
		WritableWorkbook   wbook; 
		WritableSheet   wsheet; 
		File output=new File(url); 
		if(!output.getParentFile().exists()) {
			output.getParentFile().mkdirs();}
		if(!output.exists()) {
            output.createNewFile(); 
        }
		wbook=Workbook.createWorkbook(output);
        wsheet=wbook.createSheet(createSheet,0); 
        Label titel=null;
        Label context=null;
        WritableFont headerFont=new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE); 
        WritableCellFormat headerFormat=new WritableCellFormat(headerFont);    
        WritableFont titleFont=new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED); 
        WritableCellFormat titleFormat=new WritableCellFormat(titleFont);    
        WritableFont detFont=new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); 
        WritableCellFormat detFormat=new WritableCellFormat(detFont);
	    try{
	    	 wsheet.setColumnView(0,20);
	         wsheet.setColumnView(1,20);
	         wsheet.setColumnView(2,20);
	        titel=new   Label(0,0,laber,headerFormat); 
			wsheet.addCell(titel);	        
	        for(int   i=0;i <nameList.size();i++)   { 
	        	titel=new   Label(i,1,nameList.get(i),titleFormat);
	        	 wsheet.addCell(titel); 
	        }
	        int mapListlength=mapList.size();
	        for (int i = 0; i < mapListlength; i++) {
				HashMap<Object, Object> maps = (HashMap<Object, Object>) mapList.get(i);
				for (int j = 0; j < maps.size(); j++) {
					if (maps.get(j) != null) {
						String aa = (String) maps.get(j).toString();
						context= new Label(j,i + 2,aa,detFormat);
						wsheet.addCell(context);
					}
				}
			}
	        
       }finally{
    	   	wbook.write(); 
    	    wbook.close(); 
       }
	}
	
	
	public void commonExcel(List<String> nameList,String url,List<Map>mapList,String titleName) throws Exception{
		WritableWorkbook   wbook; 
		WritableSheet   wsheet; 
		
		File output=new File(url); 
		if(!output.exists()) {
            output.createNewFile(); 
        }
		wbook=Workbook.createWorkbook(output);
        wsheet=wbook.createSheet("数据分析--"+titleName,0); 
        Label titel=null;
        Label context=null;
        WritableFont headerFont=new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE); 
        WritableCellFormat headerFormat=new WritableCellFormat(headerFont);    
        WritableFont titleFont=new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED); 
        WritableCellFormat titleFormat=new WritableCellFormat(titleFont);    
        WritableFont detFont=new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); 
        WritableCellFormat detFormat=new WritableCellFormat(detFont);
    try{ 
	    	 wsheet.setColumnView(0,20);
	         wsheet.setColumnView(1,20);
	         wsheet.setColumnView(2,20);
	        titel=new   Label(0,0,titleName,headerFormat); 
			wsheet.addCell(titel);	        
	        for(int   i=0;i <nameList.size();i++)   { 
	        	titel=new   Label(i,1,nameList.get(i),titleFormat);
	        	 wsheet.addCell(titel); 
	        }
	        int mapListlength=mapList.size();
	        for (int i = 0; i < mapListlength; i++) {
				HashMap<Object, Object> maps = (HashMap<Object, Object>) mapList.get(i);
				for (int j = 0; j < maps.size(); j++) {
					if (maps.get(j) != null) {
						String aa = (String) maps.get(j).toString();
						context= new Label(j,i + 2,aa,detFormat);
						wsheet.addCell(context);
					}
				}
			}
	        
       }finally{
    	   	wbook.write(); 
    	    wbook.close(); 
       }
	}
	
	
	public  void daoExcel(List<String> nameList,String url,List<Map>mapList,Float totalm,Long totall){
		//调用导出excel方法
		WritableWorkbook   wbook; 
		WritableSheet   wsheet; 
		File output=new File(url); 
		try {
			if(!output.exists()) {
				output.getParentFile().mkdirs();
                   output.createNewFile(); 
            }
			wbook=Workbook.createWorkbook(output);
	        wsheet=wbook.createSheet( "南航账单信息",0); 
	        Label titel=null;
	        Label context=null;
	        Label note=null;
	        Label trail=null;
	        WritableFont headerFont=new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE); 
	        WritableCellFormat headerFormat=new WritableCellFormat(headerFont);   
	        headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
	        WritableFont titleFont=new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED); 
	        WritableCellFormat titleFormat=new WritableCellFormat(titleFont);  
	        titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
	        WritableFont detFont=new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); 
	        WritableCellFormat detFormat=new WritableCellFormat(detFont); 	
	        detFormat.setAlignment(jxl.format.Alignment.CENTRE);
	        titel=new   Label(0,0,"对账信息",headerFormat); 
			wsheet.addCell(titel);	        
	        for(int   i=0;i <nameList.size();i++)   { 
	        	titel=new   Label(i,1,nameList.get(i),titleFormat);
	        	 wsheet.addCell(titel); 
	        }
	        int mapListlength=mapList.size();
	        for (int i = 0; i < mapListlength; i++) {
				HashMap<Object, Object> maps = (HashMap<Object, Object>) mapList.get(i);
				for (int j = 0; j < maps.size(); j++) {
					if (maps.get(j) != null) {
						String aa = (String) maps.get(j).toString();
						context= new Label(j,i + 2,aa,detFormat);
						wsheet.addCell(context);
					}
				}
			}
	        note=new Label(0,mapListlength+3,"",titleFormat);
	        wsheet.addCell(note);
	        trail=new Label(0,mapListlength+4,"总计："+totalm+"元，"+totall+"里程",detFormat);
	        wsheet.addCell(trail);
	        wbook.write(); 
	        wbook.close(); 
	    }catch (IOException e) {
	    	e.printStackTrace();
			LogMessage.logwrite(this.getClass(), e.getMessage());
		}catch (RowsExceededException e) {
			e.printStackTrace();
			LogMessage.logwrite(this.getClass(), e.getMessage());
		}catch (WriteException e) {
			e.printStackTrace();
			LogMessage.logwrite(this.getClass(), e.getMessage());
		} 
	}
	

	
	public  HttpServletResponse download(String path, HttpServletResponse response) {
        try  {
            //  path是指欲下载的文件的路径。 
           File file  =   new  File(path);
            //  取得文件名。 
           String filename  =  file.getName();
            //  取得文件的后缀名。 
           String ext  =  filename.substring(filename.lastIndexOf( " . " )  +   1 ).toUpperCase();
            //  以流的形式下载文件。 
           InputStream fis  =   new  BufferedInputStream( new  FileInputStream(path));
            byte [] buffer  =   new   byte [fis.available()];
           fis.read(buffer);
           fis.close();
            //  清空response 
           response.reset();
            //  设置response的Header 
           response.addHeader( " Content-Disposition " ,  " attachment;filename= "   +   new  String(filename.getBytes()));
           response.addHeader( " Content-Length " ,  ""   +  file.length());
           OutputStream toClient  =   new  BufferedOutputStream(response.getOutputStream());
           response.setContentType( " application/octet-stream " );
           toClient.write(buffer);
           toClient.flush();
           toClient.close();
       }  catch  (IOException ex) {
           ex.printStackTrace();
       }
        return  response;
   }

    public   void  downloadLocal(HttpServletResponse response,String url)  throws  FileNotFoundException {
        //  下载本地文件 
       String fileName  =   getRandomName();  //  文件的默认保存名
        //  读到流中 
       InputStream inStream  =   new  FileInputStream(url); //  文件的存放路径
        //  设置输出的格式 
       response.reset();
       //response.setCharacterEncoding("utf-8");
       response.setContentType("application/vnd.ms-excel MSEXCEL");
       response.setHeader( "Content-Disposition" ,  "attachment;filename=\""  + fileName +  "\"" );
        //  循环取出流中的数据 
        byte [] b  =   new   byte [ 100 ];
        int  len;
        try  {
            while  ((len  =  inStream.read(b))  >   0 ) {
                response.getOutputStream().write(b, 0, len);
            }
           inStream.close();
       }  catch  (IOException e) {
           LogMessage.logwrite(this.getClass(), e.getMessage());
       }
   }
    
    public String getRandomName(){
		DateFormat format = new SimpleDateFormat("yyMMddHHmmss");   
        String formatDate = format.format(new Date());        
        int random = new Random().nextInt(10000); 
        return formatDate+random+".xls";
	}

    public   void  downloadNet(HttpServletResponse response)  throws  MalformedURLException {
        //  下载网络文件 
        int  bytesum  =   0 ;
        int  byteread  =   0 ;

       URL url  =   new  URL( " windine.blogdriver.com/logo.gif " );

        try  {
           URLConnection conn  =  url.openConnection();
           InputStream inStream  =  conn.getInputStream();
           FileOutputStream fs  =   new  FileOutputStream( " c:/abc.gif " );

            byte [] buffer  =   new   byte [ 1204 ];
            //int  length;
            while  ((byteread  =  inStream.read(buffer))  !=   - 1 ) {
               bytesum  +=  byteread;
               
               fs.write(buffer,  0 , byteread);
           }
       }  catch  (FileNotFoundException e) {
           LogMessage.logwrite(this.getClass(), e.getMessage());
       }  catch  (IOException e) {
           LogMessage.logwrite(this.getClass(), e.getMessage());
       }
   } 

//支持在线打开文件的一种方式
    public   void  downLoad(String filePath, HttpServletResponse response,  boolean  isOnLine)  throws  Exception {
       File f  =   new  File(filePath);
        if  ( ! f.exists()) {
           response.sendError( 404 ,  " File not found! " );
            return ;
       }
       BufferedInputStream br  =   new  BufferedInputStream( new  FileInputStream(f));
        byte [] buf  =   new   byte [ 1024 ];
        int  len  =   0 ;

       response.reset();  //  非常重要 
        if  (isOnLine) {  //  在线打开方式 
           URL u  =   new  URL( " file:/// "   +  filePath);
           response.setContentType(u.openConnection().getContentType());
           response.setHeader( " Content-Disposition " ,  " inline; filename= "   +  f.getName());
            //  文件名应该编码成UTF-8 
       }  else  {  //  纯下载方式 
           response.setContentType( " application/x-msdownload " );
           response.setHeader( " Content-Disposition " ,  " attachment; filename= "   +  f.getName());
       }
       OutputStream out  =  response.getOutputStream();
        while  ((len  =  br.read(buf))  >   0 ) {
            out.write(buf, 0, len);
        }
       br.close();
       out.close();
   } 


    public  void exchangeDetail(List<String> detailList,String url){
    	WritableWorkbook   wbook; 
		WritableSheet   wsheet; 
		File output=new File(url); 
		try {
			wbook=Workbook.createWorkbook(output);
		
		    wsheet=wbook.createSheet( "模板",0); 
		    Label  l=null; 
		    Label  c=null; 
		    WritableFont dFont=new WritableFont(WritableFont.createFont("楷体_GB2312"),12,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE); 
		    WritableCellFormat dFormat=new WritableCellFormat(dFont);   
		    WritableFont headerFont=new WritableFont(WritableFont.createFont("黑体"),12,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); 
		    WritableCellFormat headerFormat=new WritableCellFormat(headerFont);    
		    WritableFont titleFont=new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED); 
		    WritableCellFormat titleFormat=new WritableCellFormat(titleFont);    
		    WritableFont detFont=new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); 
		    WritableCellFormat detFormat=new WritableCellFormat(detFont); 
		    //（列，行，列，行）
		    wsheet.mergeCells(0,0,1,0);//订单编号
		    l=new   Label(0,0,"订单编号",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(2,0,4,0);//订单编号（数据）
			    c=new   Label(2,0,detailList.get(0),detFormat);
			    wsheet.addCell(c);
		   
			    
		    /**第二行下表为1*/
		    wsheet.mergeCells(0,1,1,1);//商品编号
		    l=new   Label(0,1,"商品编号",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(2,1,4,1);//商品编号（数据）
			    c=new   Label(2,1,detailList.get(1),detFormat);
			    wsheet.addCell(c);
		    
		    /**第三行下表为2*/
		    wsheet.mergeCells(0,2,1,2);//商品名称
		    l=new   Label(0,2,"商品名称",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(2,2,3,2);//商品名称（数据）
			    c=new   Label(2,2,detailList.get(2),detFormat);
			    wsheet.addCell(c);  
		    wsheet.mergeCells(4,2,5,2);//单品里程
		    l=new   Label(4,2,"单品里程",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(6,2,7,2);//单品里程（数据）
			    c=new   Label(6,2,detailList.get(3),detFormat);
			    wsheet.addCell(c); 
		    wsheet.mergeCells(8,2,9,2);//单品金额
		    l=new   Label(8,2,"单品金额（元）",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(10,2,11,2);//单品金额（数据）
			    c=new   Label(10,2,detailList.get(4),detFormat);
			    wsheet.addCell(c); 
		    wsheet.mergeCells(12,2,13,2);//商品数量
		    l=new   Label(12,2,"商品数量（件）",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(14,2,15,2);//商品单金额（数据） 
			    c=new   Label(14,2,detailList.get(5),detFormat);
			    wsheet.addCell(c); 

			/**第四行下表为3*/
		    wsheet.mergeCells(0,3,1,3);//总计里程
		    l=new   Label(0,3,"总计里程",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(2,3,3,3);//总计里程（数据）
			    c=new   Label(2,3,detailList.get(6),detFormat);
			    wsheet.addCell(c); 
		    wsheet.mergeCells(4,3,5,3);//总计金额（
		    l=new   Label(4,3,"总计金额（元）",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(6,3,7,3);//总计金额（数据）
			    c=new   Label(6,3,detailList.get(7),detFormat);
			    wsheet.addCell(c); 
		    wsheet.mergeCells(8,3,9,3);//退款里程
		    l=new   Label(8,3,"退款里程",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(10,3,11,3);//退款里程（数据）
			    c=new   Label(10,3,detailList.get(8),detFormat);
			    wsheet.addCell(c); 
		    wsheet.mergeCells(12,3,13,3);//退款金额
		    l=new   Label(12,3,"退款金额（元）",dFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(14,3,15,3);//退款金额（数据）
			    c=new   Label(14,3,detailList.get(9),detFormat);
			    wsheet.addCell(c);

			    
		    /**第五行下表为4*/
		    wsheet.mergeCells(0,4,1,4);//退换货类型
		    l=new   Label(0,4,"退换货类型",headerFormat); 
		    wsheet.addCell(l); 
		    wsheet.mergeCells(2,4,3,4);//退换货类型（数据）
			    c=new   Label(2,4,detailList.get(10),detFormat);
			    wsheet.addCell(c);
		    
		    wsheet.mergeCells(5,4,6,4);//原因
		    l=new   Label(5,4,"原因",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(7,4,15,4);//原因（数据）
			    c=new   Label(7,4,detailList.get(11),detFormat);
			    wsheet.addCell(c);
		  
			
			/**第六行下表为5*/
		    wsheet.mergeCells(0,5,4,5);//选择商品外包装情况	  
		    l=new   Label(0,5,"选择商品外包装情况",headerFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(5,5,6,5);//外观
		    l=new   Label(5,5,"外观",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(7,5,8,5);//外观（数据）
			    c=new   Label(7,5,detailList.get(12),detFormat);
			    wsheet.addCell(c);   
		    wsheet.mergeCells(9,5,10,5);//外包装
		    l=new   Label(9,5,"外包装",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(11,5,15,5);//外包装（数据）
			    c=new   Label(11,5,detailList.get(13),detFormat);
			    wsheet.addCell(c);  
		    
		    /**第七行下表为6*/
		    wsheet.mergeCells(0,6,4,7);//商品配件及发票信息
		    l=new   Label(0,6,"商品配件及发票信息",headerFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(5,6,6,6);//商品配件
		    l=new   Label(5,6,"商品配件",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(7,6,15,6);//商品配件（数据）
			    c=new   Label(7,6,detailList.get(14),detFormat);
			    wsheet.addCell(c);  
		    /**第八行下表为7*/
		    wsheet.mergeCells(5,7,6,7);//是否有发票
		    l=new   Label(5,7,"是否有发票",detFormat); 
		    wsheet.addCell(l);
		    //7行“是否”发票数据
			    c=new   Label(7,7,detailList.get(15),detFormat);
			    wsheet.addCell(c);  
		    wsheet.mergeCells(8,7,9,7);//取货方式
		    l=new   Label(8,7,"取货方式",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(10,7,11,7);//取货方式（数据）
			    c=new   Label(10,7,detailList.get(16),detFormat);
			    wsheet.addCell(c);  
		    
		    /**第九行下表为8*/
		    wsheet.mergeCells(0,8,1,9);//配送信息 
		    l=new   Label(0,8,"配送信息",headerFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(2,8,3,8);//申请人姓名
		    l=new   Label(2,8,"申请人姓名",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(4,8,5,8);//申请人姓名（数据）
			    c=new   Label(4,8,detailList.get(17),detFormat);
			    wsheet.addCell(c);  
		    wsheet.mergeCells(6,8,7,8);//联系电话
		    l=new   Label(6,8,"联系电话",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(8,8,9,8);//联系电话（数据）
			    c=new   Label(8,8,detailList.get(18),detFormat);
			    wsheet.addCell(c);  
		    
		    /**第十行下表为9*/
		    wsheet.mergeCells(2,9,3,9);//邮政编码
		    l=new   Label(2,9,"邮政编码",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(4,9,5,9);//邮政编码（数据）
			    c=new   Label(4,9,detailList.get(19),detFormat);
			    wsheet.addCell(c);  
		    wsheet.mergeCells(6,9,7,9);//送货地址
		    l=new   Label(6,9,"送货地址",detFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(8,9,15,9);//送货地址（数据）
			    c=new   Label(8,9,detailList.get(20),detFormat);
			    wsheet.addCell(c);  
		    
		    
		    /**第十一行下表为10*/
		    wsheet.mergeCells(0,10,1,13);//配送信息 
		    l=new   Label(0,10,"个人账户信息",headerFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(2,10,3,10);//申请人帐号
		    l=new   Label(2,10,"申请人帐号",titleFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(4,10,6,10);//申请人帐号（数据）
			    c=new   Label(4,10,detailList.get(21),detFormat);
			    wsheet.addCell(c);  
		    
		    /**第十二行下表为11*/
		    wsheet.mergeCells(2,11,3,11);//开户银行名
		    l=new   Label(2,11,"开户银行名",titleFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(4,11,6,11);//开户银行名（数据）
			    c=new   Label(4,11,detailList.get(22),detFormat);
			    wsheet.addCell(c);  
		    
		    /**第十二行下表为12*/
		    wsheet.mergeCells(2,12,3,12);//银行帐户姓名
		    l=new   Label(2,12,"银行帐户姓名",titleFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(4,12,6,12);//银行帐户姓名（数据）
			    c=new   Label(4,12,detailList.get(23),detFormat);
			    wsheet.addCell(c);  
		    
		    /**第十二行下表为13*/
		    wsheet.mergeCells(2,13,3,13);//银行卡号
		    l=new   Label(2,13,"银行卡号",titleFormat); 
		    wsheet.addCell(l);
		    wsheet.mergeCells(4,13,6,13);//银行卡号（数据）
			    c=new   Label(4,13,detailList.get(24),detFormat);
			    wsheet.addCell(c);  
		    
		    	  
		    wbook.write(); 
		    wbook.close(); 
	    } catch (Exception e) {
			LogMessage.logwrite(this.getClass(), e.getMessage());
		} 
    	
    }
    

}
